﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Windows.Forms;


namespace AlphaSolutionsGP
{
    public class Student : Person
    {
        MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");        

        #region properties
        //Id of the student
        private int _id;
        // whether or not the student is active or not
        private string _status;
        //personal information of the student
        //level of the course
        private int _level;
        private string _dateEnrolled;
        private string _dateGraduated;
        //password to login
        private string _password;
        //the path for the image of the student
        private string _imageLocation;
        //course code of course the student is studying
        private string _courseCode;
        private List<Qualification> Qualification = new List<Qualification>();
        #endregion


        #region constructors 
        //blank constructor
        public Student()
        {

        }
        //Constructor that includes properties
        public Student(int ID, string dateEnrolled, int level, string dateGraduated, string password, string imageLocation, string courseCode, string nationality, string hometown, string status, string firstName, string middleName, string surname, string telephoneNumber, string pEmail, string aEmail,
            string postCode, string streetAddress, string city, string mobileNumber, string DOB)
            : base(firstName, middleName, surname, DOB, nationality,hometown,streetAddress,postCode,city,mobileNumber,telephoneNumber,pEmail,aEmail)
        {            
            this._id = ID;
            this._status = status;
            this._dateEnrolled = dateEnrolled;
            this._dateGraduated = dateGraduated;
            this._password = password;
            this._imageLocation = imageLocation;
            this._courseCode = courseCode;
            this._level = level;                  
        }
        #endregion


        #region methods
        public void createStudent()
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");        

            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }

            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "insert into STUDENT values('" + this._id + "','" + this._courseCode + "','" + this._level + "','" + this.FirstName + "','" + this.MiddleName + "','" + this.Surname + "','" + convertToMySQLDate(this.DOB) + "','" + this.Nationality + "','" + this.HomeTown + "','" + this.StreetAddress + "','" + this.PostCode + "','" + this.City + "','" + this.MobileNumber + "','" + this.TelephoneNumber + "','" + convertToMySQLDate(this.DateEnrolled) + "','" + convertToMySQLDate(this.DateGraduated) + "','" + this._status + "','" + this.EmailAddress + "','" + this.AssignedEmail + "','" + this._password + "','" + this._imageLocation + "')";
            // COURSE_course_Code, level, name, middle_name, surname, DOB, nationality, home_town, street_address, postcode, city, mobile_number, telephone_number, date_enrolment, date_graduation, status, email_address, assigned_email, password, image_location
            MySqlDataReader run = cmd.ExecuteReader();
            connection.Close();
        }
        public void amendStudent()
        {
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE STUDENT SET COURSE_course_Code='" + _courseCode + "', level='" + _level + "', name='" + FirstName + "', middle_name='" + MiddleName + "', surname='" + Surname + "', DOB='" + convertToMySQLDate(this.DOB) + "', nationality='" + Nationality + "', home_town='" + HomeTown + "', street_address='" + StreetAddress + "', postcode='" + PostCode + "', city='" + City + "', mobile_number='" + MobileNumber + "', telephone_number='" + TelephoneNumber + "', date_enrolment='" + convertToMySQLDate(DateEnrolled) + "', date_graduation='" + convertToMySQLDate(DateGraduated) + "', status='" + Status + "', email_address='" + EmailAddress + "', assigned_email='" + AssignedEmail + "', password='" + Password + "', image_location='" + ImageLocation + "' WHERE student_ID=" + ID;
                MySqlDataReader run = cmd.ExecuteReader();
                connection.Close();
            }
            catch
            {
                MessageBox.Show("Connection failed");
            }
            
        }
        public void deleteStudent()
        {
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "delete from STUDENT where student_ID='" + this._id + "'";
                MySqlDataReader run = cmd.ExecuteReader();
                MessageBox.Show("Student: " + this.FirstName + " " + this.Surname + " has been deleted successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                connection.Close();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            
        }

        //this method takes a string date as argument and convert it to mysql format date
        //used to convert the date when it comes to do INSERT into tables
        public static string convertToMySQLDate(string date)
        {
            //string dateToString = Convert.ToString(date);
            DateTime toDate = DateTime.Parse(date);
            string finalDate = toDate.Date.ToString("yyyy/MM/dd");

            return finalDate;
        }

        #endregion


        #region getters and setters
        
        public string ImageLocation
        {
            get { return _imageLocation; }
            set { _imageLocation = value; }
        }

        public string Password
        {
            get { return _password; }
            set { _password = value; }
        }


        public string DateGraduated
        {
            get { return _dateGraduated; }
            set { _dateGraduated = value; }
        }


        public string DateEnrolled
        {
            get { return _dateEnrolled; }
            set { _dateEnrolled = value; }
        }


        public int Level
        {
            get { return _level; }
            set { _level = value; }
        }

        public string CourseCode
        {
            get { return _courseCode; }
            set { _courseCode = value; }
        }     

        public string Status
        {
            get { return _status; }
            set { _status = value; }
        }
        
        public int ID
        {
            get { return _id; }
            set { _id = value; }
        }
        #endregion

    }
}
